#!bin/bash

if [ -n "$1" ] ; then
  data_date=$1
else
  data_date=`date -d '-1 day' +%F`
fi


DIM_BRAND_SERIES_MODEL_KEY_WORD_SQL="
WITH
    t1 AS (
        SELECT
            keyword, normalized_keyword, brand_id, brand_name,
            series_id, series_name, model_id, model_name, price_range,
            is_brand_search, is_series_search, is_model_search, is_price_search,
            status, create_time, update_time
        FROM car_home_warehouse.keyword_mapping
        WHERE dt = '${data_date}'
    ),
    t2 AS (
        SELECT
            brand_id, brand_name, brand_initial, brand_logo, country,
            brand_level, parent_brand_id, is_common, status, create_time, update_time
        FROM car_home_warehouse.car_brand_information
        WHERE dt = '${data_date}'
    ),
    t3 AS (
        SELECT
            series_id, series_name, brand_id, brand_name, manufacturer,
            vehicle_type, price_range, status, create_time, update_time
        FROM car_home_warehouse.car_series_information
        WHERE dt = '${data_date}'
    ),
    t4 AS (
        SELECT
            model_id, model_name, series_id, series_name, brand_id, brand_name,
            year_type, guide_price, status, create_time, update_time
        FROM car_home_warehouse.model_information_sheet
        WHERE dt = '${data_date}'
    )
INSERT INTO car_home_warehouse.dim_brand_series_model_key_word PARTITION (dt = '${data_date}')
SELECT
    t1.keyword
    ,t1.normalized_keyword
    ,t1.brand_id
    ,t1.brand_name
    ,t2.country AS brand_country
    ,t2.brand_level
    ,t2.parent_brand_id
    ,t2.is_common AS brand_is_common
    ,t2.status AS brand_status
    ,t1.series_id
    ,t1.series_name
    ,t3.manufacturer AS series_manufacturer
    ,t3.vehicle_type AS series_vehicle_type
    ,t3.status AS series_status
    ,t1.model_id
    ,t1.model_name
    ,t4.year_type AS model_year_type
    ,t4.guide_price AS model_guide_price
    ,t4.status AS model_status
    ,t1.price_range
FROM t1 LEFT JOIN t2 ON t1.brand_id = t2.brand_id
        LEFT JOIN t3 ON t1.series_id = t3.series_id
        LEFT JOIN t4 ON t1.model_id = t4.model_id
;
"

/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -p 123456 -e "${DIM_BRAND_SERIES_MODEL_KEY_WORD_SQL}"
